const { Router } = require('express')
const { getEveryDay, getFirstDate4ThisMonth, getToday, getWeekAgoDate } = require('../utils')
const db = require('../utils/db')
const xlsx = require('node-xlsx')
const axios = require('axios')

const router = new Router()

// 根据时间范围查询文件上传数量
router.get('/getFileCountByDateRange', (req, res) => {
  const { start, end } = req.query
  const sql = `select to_char(f.update_time, 'yyyy-mm-dd') "updateTime", count(id) from t_files f where update_time between $1 and $2 
    group by to_char(f.update_time, 'yyyy-mm-dd') 
    order by "updateTime";`
  const values = [start + ' 00:00:00', end + ' 23:59:59']
  db.query({ text: sql, values }, (err, result) => {
    // 获取传入时间范围的总天数
    const dateList = getEveryDay(start, end)
    const newRows = []
    dateList.forEach((date, index) => {
      const findDate = result.rows.find(item => item.updateTime === date)
      if (findDate) {
        newRows.push({ ...findDate, count: Number(findDate.count) })
      } else {
        newRows.push({
          updateTime: date,
          count: 0
        })
      }
    })
    result.rows = newRows
    if (err) {
      console.log('err: ', err.message)
    } else {
      res.send({
        code: 200,
        message: '查询成功',
        data: result.rows
      })
    }
  })
})

// 获取文件类型统计
router.get('/getFileTypeStat', (req, res) => {
  const sql = `select mimetype, count(mimetype), round(count(mimetype)::numeric/(select count(mimetype) from t_files)*100,2) proportion from t_files f 
    group by mimetype 
    order by case
      when mimetype like 'image/%' THEN 1
      when mimetype like 'video/%' THEN 2
      when mimetype like '%.document' THEN 3
      when mimetype like '%.sheet' THEN 4
      when mimetype like '%.presentation' THEN 5
      when mimetype = 'application/octet-stream' THEN 6
      when mimetype = 'text/plain' THEN 7
    end
  ;`

  db.query(sql, (err, result) => {
    const newRows = [
      {
        name: 'image',
        value: 0,
        proportion: 0
      },
      {
        name: 'video',
        value: 0,
        proportion: 0
      },
      {
        name: 'word',
        value: 0,
        proportion: 0
      },
      {
        name: 'excel',
        value: 0,
        proportion: 0
      },
      {
        name: 'ppt',
        value: 0,
        proportion: 0
      },
      {
        name: 'markdown',
        value: 0,
        proportion: 0
      },
      {
        name: 'txt',
        value: 0,
        proportion: 0
      },
      {
        name: 'other',
        value: 0,
        proportion: 0
      }
    ]
    let totalFileCount = 0
    for (const item of result.rows) {
      item.count = Number(item.count)
      item.proportion = Number(item.proportion)
      totalFileCount += Number(item.count)
      if (item.mimetype.startsWith('image/')) {
        newRows[0].value += item.count
        newRows[0].proportion += item.proportion
      } else if (item.mimetype.startsWith('video/')) {
        newRows[1].value += item.count
        newRows[1].proportion += item.proportion
      } else if (item.mimetype.endsWith('.document')) {
        newRows[2].value += item.count
        newRows[2].proportion += item.proportion
      } else if (item.mimetype.endsWith('.sheet')) {
        newRows[3].value += item.count
        newRows[3].proportion += item.proportion
      } else if (item.mimetype.endsWith('.presentation')) {
        newRows[4].value += item.count
        newRows[4].proportion += item.proportion
      } else if (item.mimetype === 'application/octet-stream') {
        newRows[5].value += item.count
        newRows[5].proportion += item.proportion
      } else if (item.mimetype === 'text/plain') {
        newRows[6].value += item.count
        newRows[6].proportion += item.proportion
      } else {
        newRows[7].value += item.count
        newRows[7].proportion += item.proportion
      }
    }

    result.rows = newRows

    if (err) {
      console.log('err: ', err.message)
    } else {
      res.send({
        code: 200,
        message: '查询成功',
        data: result.rows,
        total: totalFileCount
      })
    }
  })
})

// 获取本月新增文件数量
router.get('/getFileCountAdd4ThisMonth', (req, res) => {
  const start = getFirstDate4ThisMonth()
  const sql = `select count(*) from t_files where update_time >= $1;`
  const values = [start + ' 00:00:00']
  db.query({ text: sql, values }, (err, result) => {
    if (err) {
      console.log('err: ', err.message)
    } else {
      res.send({
        code: 200,
        message: '查询成功',
        data: Number(result.rows[0].count)
      })
    }
  })
})

// 导出 excel 报表
router.get('/exportExcel', async (req, res) => {
  // 获取 excel 所需的数据
  // 今天日期
  const today = getToday()
  const weekAgoDate = getWeekAgoDate()
  const fileCountList = (await axios.get(`http://${req.headers.host}/dataOverview/getFileCountByDateRange?start=${weekAgoDate}&end=${today}`)).data.data
  // 本月新增文件数量
  const fileCountAdd4ThisMonth = (await axios.get(`http://${req.headers.host}/dataOverview/getFileCountAdd4ThisMonth`)).data.data
  const fileTypeData = (await axios.get(`http://${req.headers.host}/dataOverview/getFileTypeStat`)).data
  const fileTypeList = fileTypeData.data
  // 解析 excel
  const dataByParse = xlsx.parse(process.cwd() + '/public/template/文件统计报表.xlsx')

  dataByParse[0].data[2][1] = today
  dataByParse[0].data[4][1] = fileCountList.at(-1).count
  dataByParse[0].data[4][3] = fileTypeData.total
  dataByParse[0].data[5][1] = eval(fileCountList.map(item => item.count).join('+'))
  dataByParse[0].data[5][3] = fileCountAdd4ThisMonth
  dataByParse[0].data[7][1] = `${fileTypeList[0].value}（占比：${fileTypeList[0].proportion}）`
  dataByParse[0].data[7][3] = `${fileTypeList[1].value}（占比：${fileTypeList[1].proportion}）`
  dataByParse[0].data[8][1] = `${fileTypeList[2].value}（占比：${fileTypeList[2].proportion}）`
  dataByParse[0].data[8][3] = `${fileTypeList[3].value}（占比：${fileTypeList[3].proportion}）`
  dataByParse[0].data[9][1] = `${fileTypeList[4].value}（占比：${fileTypeList[4].proportion}）`
  dataByParse[0].data[9][3] = `${fileTypeList[5].value}（占比：${fileTypeList[5].proportion}）`
  dataByParse[0].data[10][1] = `${fileTypeList[6].value}（占比：${fileTypeList[6].proportion}）`
  dataByParse[0].data[10][3] = `${fileTypeList[7].value}（占比：${fileTypeList[7].proportion}）`

  const sheetOptions = {
    // 指定sheet1相应宽度
    '!cols': [{ wch: 25 }, { wch: 25 }, { wch: 25 }, { wch: 25 }],
    '!merges': [
      { s: { c: 0, r: 0 }, e: { c: 3, r: 1 } },
      { s: { c: 1, r: 2 }, e: { c: 3, r: 2 } },
      { s: { c: 0, r: 3 }, e: { c: 3, r: 3 } },
      { s: { c: 0, r: 6 }, e: { c: 3, r: 6 } }
    ]
  }

  // 最初一步，应用xlsx插件自带的build办法将解析后的数据转换成为excel表格（buffer模式的流文件）
  // 以流文件的模式返回给前端，前端接管解析下载即可
  const buffer = xlsx.build(dataByParse, { sheetOptions })

  res.setHeader('Content-Type', 'application/vnd.openxmlformats;charset=utf-8')
  res.setHeader('Content-Disposition', `attachment;filename=${encodeURI('文件统计报表')}.xlsx`)

  res.end(buffer)
})

module.exports = router
